package dao;

import helper.StringHelper;

import java.sql.ResultSet;
import java.util.ArrayList;

import util.Config;

import model.CountryModel;

/**
 * DB-Lesezugriffe auf CountryModel
 * 
 * @author Katharina Matei
 * @Version 1.0.0
 */

public class CountryDAO implements CountryDAOInterface {

	/**
	 * Fuegt ein CountryModel in die Datenbank ein
	 * 
	 * @param CountryModel
	 * @throws DBException
	 */
	@Override
	public void insert(CountryModel country) throws DBException {
		String dbStatement = "INSERT INTO countrymodel (country_id, name) VALUES ('"
				+ country.getCountryID() + "','" + country.getName() + "' )";
		executeDBStatement(dbStatement);
	}

	/**
	 * Aendert die Namen der Countries, die der uebergebenen country_id
	 * entsprechen
	 * 
	 * @param CountryModel
	 * @throws DBException
	 */
	@Override
	public void update(CountryModel country) throws DBException {
		String dbStatement = "UPDATE countrymodel set name = '"
				+ country.getName() + "' WHERE country_id = '"
				+ country.getCountryID() + "'";
		executeDBStatement(dbStatement);
	}

	/**
	 * Loeschen der Countries aus der Datenbank, die der uebergebenen country_id
	 * entsprechen
	 * 
	 * @param CountryModel
	 * @throws DBException
	 */
	@Override
	public void delete(CountryModel country) throws DBException {
		String dbStatement = "DELETE FROM countrymodel WHERE country_id = '"
				+ country.getCountryID() + "'";
		executeDBStatement(dbStatement);
	}

	/**
	 * Liest einen Country-Datensatz zu einer country_id aus der Datenbank
	 * 
	 * @param String countryID
	 * @return CountryModel
	 * @throws DBException
	 */
	@Override
	public CountryModel readCountry(String countryID) throws DBException {
		CountryModel country = null;
		String dbStatement = "SELECT * FROM countrymodel WHERE country_id = '"
				+ StringHelper.addSlashes(countryID) + "'";
		try {
			ResultSet resultSet = DBAccess.getInstance().query(dbStatement);
			while (resultSet.next()) {
				country = new CountryModel("");
				country.setCountryID(resultSet.getString("country_id"));
				country.setName(resultSet.getString("name"));
			}
		} catch (Exception e) {
			if (Config.DEBUG) {
				e.printStackTrace();
			}
			throw new DBException("Fehler in : " + dbStatement, e);
		}
		return country;
	}

	/**
	 * Liest alle Countries aus der Datenbank
	 * 
	 * @return ArrayList<CountryModel>
	 * @throws DBException
	 */
	@Override
	public ArrayList<CountryModel> getCountries() throws DBException {
		String dbStatement = "SELECT * FROM countrymodel ORDER BY name ";
		return getDBDaten(dbStatement);
	}

	/**
	 * Filtert Countries aus Citys
	 * 
	 * @return ArrayList<CountryModel>
	 * @throws DBException
	 */
	@Override
	public ArrayList<CountryModel> getCountryModelAll() throws DBException {
		ArrayList<CountryModel> list = null;
		String dbStatement = "SELECT DISTINCT country_id, country_name FROM citymodel GROUP BY country_id, country_name ";
		try {
			ResultSet resultSet = DBAccess.getInstance().query(dbStatement);
			int columns = resultSet.getMetaData().getColumnCount();
			list = new ArrayList<CountryModel>(columns);
			while (resultSet.next()) {
				CountryModel countrymodel = new CountryModel("");
				countrymodel.setCountryID(resultSet.getString("country_id"));
				countrymodel.setName(resultSet.getString("country_name"));
				list.add(countrymodel);
			}

		} catch (Exception e) {
			if (Config.DEBUG) {
				e.printStackTrace();
			}
			throw new DBException("Fehler in : " + dbStatement, e);
		}
		return list;
	}

	/**
	 * Liest Countries aus der DB zu country_name
	 * 
	 * @param CountryModel
	 * @return ArrayList<CountryModel>
	 * @throws DBException
	 */
	@Override
	public ArrayList<CountryModel> getCountryByCountry(CountryModel country)
			throws DBException {
		String dbStatement = "SELECT * FROM countrymodel WHERE name LIKE '"
				+ country.getName() + "'";
		return getDBDaten(dbStatement);
	}

	/**
	 * Fuehrt die DB-Anweisung aus
	 * 
	 * @param String dbStatement
	 * @throws DBException
	 */

	private void executeDBStatement(String dbStatement) throws DBException {
		try {
			DBAccess.getInstance().execute(dbStatement);
		} catch (Exception e) {
			if (Config.DEBUG) {
				e.printStackTrace();
			}
			throw new DBException("Fehler in : " + dbStatement, e);
		}
	}

	/**
	 * Fuellt ArrayList mit Daten aus der DB
	 * 
	 * @param String dbStatement
	 * @return ArrayList<CountryModel>
	 * @throws DBException
	 */
	private ArrayList<CountryModel> getDBDaten(String dbStatement)
			throws DBException {
		ArrayList<CountryModel> list = null;
		try {
			ResultSet resultSet = DBAccess.getInstance().query(dbStatement);
			int columns = resultSet.getMetaData().getColumnCount();
			list = new ArrayList<CountryModel>(columns);
			while (resultSet.next()) {
				CountryModel obj = new CountryModel("");
				obj.setCountryID(resultSet.getString("country_id"));
				obj.setName(resultSet.getString("name"));
				list.add(obj);
			}
		} catch (Exception e) {
			if (Config.DEBUG) {
				e.printStackTrace();
			}
			throw new DBException("Fehler in : " + dbStatement, e);
		}
		return list;
	}

}
